// 引入需要的模块
var fs = require('fs');
var ejs = require('ejs');
var mysql = require('mysql');
var express = require('express');
var cookieParser = require('cookie-parser');
var bodyParser = require('body-parser');

// 连接MySQL数据库
var connection = mysql.createConnection({
    host: 'localhost',
    port: "3306",
    user: 'root',
    password: '',
    database: 'Library'
});

// 创建服务器
var app = express();
app.use(cookieParser());
app.use(bodyParser.urlencoded({ extended: false }));

// 根路径处理
app.get('/', function (request, response) {
    if (request.cookies.auth) { // 如果已登录，根据角色重定向
        response.redirect('/user-book-list'); // 默认重定向到用户图书列表
    } else {
        response.redirect('/login'); // 未登录重定向到登录页面
    }
});

// 登录页面路由，读取并返回login.html文件
app.get('/login', function (request, response) {
    fs.readFile('login.html', 'utf8', function (error, data) {
        response.send(data.toString());
    });
});

// 处理登录表单提交
app.post('/login', function (request, response) {
    var user = request.body.user;
    var pass = request.body.pass;

    // 查询数据库验证用户信息
    connection.query('SELECT * FROM users WHERE user = ? AND pass = ?', [user, pass], function (error, results) {
        if (error) {
            console.error(error);
            return response.redirect('/login?message=error');
        }

        if (results.length > 0) {
            var username = results[0].user;
            var role = results[0].role;

            // 设置 cookies
            response.cookie('auth', true);
            response.cookie('userid', results[0].userid);
            response.cookie('username', username);
            response.cookie('role', role);

            // 根据角色重定向
            if (role === 'admin') {
                response.redirect('/admin-book-list');
            } else {
                response.redirect('/user-book-list');
            }
        } else {
            response.redirect('/login?message=invalid');
        }
    });
});

// 显示注册页面
app.get('/register', function (request, response) {
    fs.readFile('register.html', 'utf8', function (error, data) {
        response.send(data.toString());
    });
});

// 处理注册表单提交
app.post('/register', function (request, response) {
    var user = request.body.user;
    var pass = request.body.pass;

    connection.query('SELECT * FROM users WHERE user = ?', [user], function (error, results) {
        if (error) {
            console.error(error);
            response.redirect('/register?message=error'); // 如果出现错误，重定向回注册页面并传递消息
            return;
        }
        if (results.length > 0) {
            response.redirect('/register?message=exists'); // 用户已存在
        } else {
            connection.query('INSERT INTO users (user, pass) VALUES (?, ?)', [user, pass], function (error, results) {
                if (error) {
                    console.error(error);
                    response.redirect('/register?message=error'); // 如果出现错误，重定向回注册页面并传递消息
                    return;
                }
                response.redirect('/login?message=success'); // 注册成功
            });
        }
    });
});

// 显示普通用户图书列表
app.get('/user-book-list', function (request, response) {
    if (request.cookies.auth) { // 检查是否存在auth cookie
        fs.readFile('user-book-list.html', 'utf8', function (error, data) {
            connection.query('SELECT * FROM books', function (error, results) {
                // 传递用户信息给模板
                response.send(ejs.render(data, {
                    data: results,
                    username: request.cookies.username, // 获取用户名
                    role: request.cookies.role // 获取角色
                }));
            });
        });
    } else {
        response.redirect('/login');
    }
});

// 显示管理员图书列表
app.get('/admin-book-list', function (request, response) {
    if (request.cookies.auth) { // 检查是否存在auth cookie
        fs.readFile('admin-book-list.html', 'utf8', function (error, data) {
            connection.query('SELECT * FROM books', function (error, results) {
                // 传递用户信息给模板
                response.send(ejs.render(data, {
                    data: results,
                    username: request.cookies.username, // 获取用户名
                    role: request.cookies.role // 获取角色
                }));
            });
        });
    } else {
        response.redirect('/login');
    }
});

// 借阅图书
app.get('/borrow/:bookid', function (request, response) {
    if (request.cookies.auth) {
        var userid = request.cookies.userid; // 从 cookie 中获取 userid
        var bookid = request.params.bookid;
        var borrowDate = new Date();

        // 检查用户是否已经借阅该书
        connection.query('SELECT * FROM borrow_records WHERE userid = ? AND bookid = ? AND return_date IS NULL', [userid, bookid], function (error, borrowResult) {
            if (error) {
                console.error(error);
                response.redirect('/user-book-list'); // 发生错误时重定向
                return;
            }
            if (borrowResult.length > 0) {
                response.redirect('/user-book-list?message=您已经借阅了这本书，无法再次借阅。&success=false'); // 提示用户已借阅
                return;
            }

            // 检查库存
            connection.query('SELECT stock FROM books WHERE bookid = ?', [bookid], function (error, stockResult) {
                if (error) {
                    console.error(error);
                    response.redirect('/user-book-list?message=发生错误，请重试。&success=false');
                    return;
                }
                if (stockResult.length === 0 || stockResult[0].stock <= 0) {
                    response.redirect('/user-book-list?message=库存不足，无法借阅该书。&success=false'); // 库存不足提示
                    return;
                }

                // 开始事务
                connection.beginTransaction(function(err) {
                    if (err) throw err;

// 插入借阅记录
                    connection.query('INSERT INTO borrow_records (userid, bookid, borrow_date) VALUES (?, ?, ?)',
                        [userid, bookid, borrowDate], function (error) {
                            if (error) {
                                return connection.rollback(function() {
                                    console.error(error);
                                    response.redirect('/user-book-list?message=发生错误，请重试。&success=false');
                                });
                            }

                            // 更新库存
                            connection.query('UPDATE books SET stock = stock - 1, borrow_count = borrow_count + 1 WHERE bookid = ?', [bookid], function (error) {
                                if (error) {
                                    return connection.rollback(function() {
                                        console.error(error);
                                        response.redirect('/user-book-list?message=发生错误，请重试。&success=false');
                                    });
                                }
                                connection.commit(function(err) {
                                    if (err) {
                                        return connection.rollback(function() {
                                            console.error(err);
                                            response.redirect('/user-book-list?message=发生错误，请重试。&success=false');
                                        });
                                    }
                                    response.redirect('/user-book-list?message=借阅成功！&success=true');
                                });
                            });
                        });
                });
            });
        });
    } else {
        response.redirect('/login');
    }
});

// 归还图书
app.get('/return/:bookid', function (request, response) {
    if (request.cookies.auth) {
        var userid = request.cookies.userid; // 从 cookie 中获取 userid
        var bookid = request.params.bookid;
        var returnDate = new Date();

        // 开始事务
        connection.beginTransaction(function(err) {
            if (err) throw err;

            // 更新借阅记录
            connection.query('UPDATE borrow_records SET return_date = ? WHERE userid = ? AND bookid = ? AND return_date IS NULL', [returnDate, userid, bookid], function (error, results) {
                if (error) {
                    return connection.rollback(function() {
                        console.error(error);
                        response.redirect('/user-book-list'); // 发生错误时重定向
                    });
                }

                // 检查是否成功更新
                if (results.affectedRows === 0) {
                    return connection.rollback(function() {
                        response.redirect('/user-book-list?message=未找到有效的借阅记录。&success=false');
                    });
                }

                // 更新库存
                connection.query('UPDATE books SET stock = stock + 1 WHERE bookid = ?', [bookid], function (error) {
                    if (error) {
                        return connection.rollback(function() {
                            console.error(error);
                            response.redirect('/user-book-list'); // 发生错误时重定向
                        });
                    }
                    connection.commit(function(err) {
                        if (err) {
                            return connection.rollback(function() {
                                console.error(err);
                                response.redirect('/user-book-list'); // 发生错误时重定向
                            });
                        }
                        response.redirect('/user-book-list?message=归还成功！&success=true');
                    });
                });
            });
        });
    } else {
        response.redirect('/login');
    }
});

// 显示借阅记录
app.get('/borrow-records/:bookid', function (request, response) {
    if (request.cookies.auth) {
        let bookid = request.params.bookid;

        connection.query('SELECT * FROM borrow_records WHERE bookid = ?', [bookid], function (error, results) {
            if (error) {
                console.error(error);
                response.status(500).send('Internal Server Error');
                return;
            }
            fs.readFile('borrow-records.html', 'utf8', function (error, data) {
                response.send(ejs.render(data, {
                    data: results,
                    bookid: bookid
                }));
            });
        });
    } else {
        response.redirect('/login');
    }
});

// 删除数据
app.get('/delete/:id', function (request, response) {
    connection.query('DELETE FROM books WHERE bookid=?', [request.params.id], function () {
        response.redirect('/admin-book-list');
    });
});

// 显示添加图书的表单
app.get('/insert', function (request, response) {
    fs.readFile('book-insert.html', 'utf8', function (error, data) {
        response.send(data);
    });
});

// 处理添加图书的表单提交
app.post('/insert', function (request, response) {
    var body = request.body;
    connection.query('INSERT INTO books (bookname, author, press,stock) VALUES (?, ?, ?,?)', [
        body.bookname, body.author, body.press,body.stock
    ], function () {
        response.redirect('/admin-book-list');
    });
});

// 显示编辑图书的表单
app.get('/edit/:id', function (request, response) {
    fs.readFile('book-edit.html', 'utf8', function (error, data) {
        connection.query('SELECT * FROM books WHERE bookid = ?', [
            request.params.id
        ], function (error, result) {
            response.send(ejs.render(data, {
                data: result[0]
            }));
        });
    });
});

// 处理编辑图书的表单提交
app.post('/edit/:id', function (request, response) {
    var body = request.body;
    connection.query('UPDATE books SET bookname=?, author=?, press=?,stock=? WHERE bookid=?', [body.bookname, body.author, body.press,body.stock, request.params.id], function () {
        response.redirect('/admin-book-list');
    });
});

// 添加查询图书功能
app.post('/search', function (request, response) {
    if (!request.cookies.auth) {
        return response.redirect('/login'); // 确保用户已登录
    }

    var userId = request.cookies.userid; // 获取用户 ID
    var query = request.body.query;

    // 查询用户角色
    connection.query('SELECT role FROM users WHERE userid = ?', [userId], function (error, roleResult) {
        if (error) {
            console.error("Error retrieving role:", error);
            return response.status(500).send('Internal Server Error');
        }

        if (roleResult.length === 0) {
            return response.redirect('/login');
        }

        var role = roleResult[0].role; // 获取用户角色
        var username = request.cookies.username; // 从 cookie 中获取用户名

        // 检查查询内容
        if (!query) {
            return response.status(400).send('Query cannot be empty');
        }

        connection.query('SELECT * FROM books WHERE bookname LIKE ? OR author LIKE ? OR press LIKE ?', [
            `%${query}%`, `%${query}%`, `%${query}%`
        ], function (error, results) {
            if (error) {
                console.error("Error executing book search query:", error);
                return response.status(500).send('Internal Server Error');
            }

            // 根据角色重定向到不同的页面
            let templateFile = role === 'admin' ? 'admin-book-list.html' : 'user-book-list.html';

            fs.readFile(templateFile, 'utf8', function (error, data) {
                if (error) {
                    console.error("Error reading template file:", error);
                    return response.status(500).send('Internal Server Error');
                }
                // 传递 username 和 role
                response.send(ejs.render(data, { data: results, username: username, role: role }));
            });
        });
    });
});

// 退出登录路由
app.get('/logout', function (request, response) {
    // 清除用户的认证 cookie
    response.clearCookie('auth');
    response.clearCookie('user');
    response.clearCookie('username');
    response.clearCookie('role');
    response.redirect('/login'); // 重定向到登录页面
});

// 启动服务器
app.listen(52273, function () {
    console.log('服务器运行在 http://127.0.0.1:52273');
});

